﻿#----------------------------------------------------------------------------
# Name:         config.py
# Purpose:      The system configuration
#
# Author:       Zhang Kun
#
# Created:      1/21/08
# Copyright:    (c) 2007-2008 HanMingSoft, Inc.
# License:      Apache License
#----------------------------------------------------------------------------
# -*- coding: utf-8 -*-

import sqlite3 as sqlite

class Data:
    def __init__(self):
        self.conn = sqlite.connect("datainfo.s3db")
        #self.conn.row_factory = sqlite.Row
    def __del__(self):
        self.conn.close()
        
    def fetchall(self, sql):
        try:
            print sql
            return self.conn.execute(sql).fetchall()
        except:
            return None

    def fetchone(self, sql):
        try:
            print sql
            cur = self.conn.execute(sql)
            return self.conn.execute(sql).fetchone()
            # ret ={}
            # for row in cur:
                # print type(row)
                # for k, v in row:
                    # ret[k] = v
                
            # print ret
            # return cur[0]
        except:
            return None

    def execute(self, sql, param=None):
        try:
            print sql
            self.conn.execute(sql, param)
            return self.conn.commit()
        except:
            print 'execute error.'
            self.conn.rollback()
            return None
            
#    def GetAll(self, fields=[]):
#        if not hasattr(self, 'tablename'):
#            return
#
#        if len(fields) == 0:
#            sql = "select * from %s" % self.tablename
#            return self.fetchall(sql)
#        else:
#            sql = "select " + "%s," * len(fields)
#            sql = sql[:-1] + " from %s" % self.tablename
#            return self.fetchall(sql % tuple(fields))

    def GetAll(self, fields=[], order=None):
        return self.GetMany(fields, order=order)

    def GetMany(self, fields=[], where=None, order=None):
        if not hasattr(self, 'tablename'):
            return
        if len(fields) == 0:
            sql = "select * from %s" % self.tablename
        else:
            sql = "select " + "%s," * len(fields)
            sql = sql[:-1] + " from %s" % self.tablename
        if where:
            sql += " where %s" % where
        if order:
            sql += " order by %s" % order

        if len(fields) == 0:
            return self.fetchall(sql)
        else:
            return self.fetchall(sql % tuple(fields))

    def GetById(self, id, fields=[]):
        if not hasattr(self, 'tablename'):
            return
        if not id:
            return

        if len(fields) == 0:
            sql = "select * from %s where id=%d" % (self.tablename, id)
            return self.fetchone(sql)
        else:
            sql = "select " + "%s," * len(fields)
            sql = sql[:-1] + " from %s where id=%d" % (self.tablename, id)
            return self.fetchone(sql % tuple(fields))

    def Insert(self, val={}):
        if not hasattr(self, 'tablename'):
            return
        if len(val) == 0:
            return
        sql = 'insert into %s (' % self.tablename
        field = ''
        valist = []
        for fld in val.keys():
            v = val[fld]
            if v:
                # valType = type(v)
                # if valType in [str, unicode]:
                    # field += "'%s',"
                # elif valType in [int, long]:
                    # field += "%d,"
                # elif valType in [float]:
                    # field += "%f,"
                # else:
                    # print valType
                    # continue
                sql += fld + ','
                field += '?,'
                valist.append(v)
        
        sql = sql[:-1] + ') values(' + field
        sql = sql[:-1] + ')'
        
        #print '---' + sql % tuple(valist)
        #return self.execute(sql % tuple(valist))
        return self.execute(sql, tuple(valist))

    def UpdateById(self, id, val={}):
        if not hasattr(self, 'tablename'):
            return
        if len(val) == 0:
            return
        sql = 'update %s set ' % self.tablename
        field = ''
        valist = []
        for fld in val.keys():
            v = val[fld]
            if v:
                # valType = type(v)
                # if valType in [str, unicode]:
                    # field += fld + "='%s',"
                # elif valType in [int, long]:
                    # field += fld + "=%d,"
                # elif valType in [float]:
                    # field += fld + "=%f,"
                # else:
                    # print valType
                    # continue
                field += fld + "=?,"
                valist.append(v)
        sql += field
        #sql = sql[:-1] + ' where id=%d'
        sql = sql[:-1] + ' where id=?'
        valist.append(id)

        #print '---' + sql % tuple(valist)
        #return self.execute(sql % tuple(valist)) 
        return self.execute(sql, tuple(valist))
    
    def DeleteById(self, id):
        pass

class User(Data):
    tablename = 'users'
    def GetRoles(self, name):
        sql = "select role from roles where user_id = (select id from users where name='%s')" % name
        return self.fetchall(sql)

    def Check(self, name, password):
        sql = "select 'X' from users where name='%s' and password='%s'" % (name, password)
        rows = self.fetchone(sql)
        return rows and len(rows)>0

class Customer(Data):
    tablename = 'customers'

if __name__ == "__main__":
    user = User()
    print user.GetAll()
    print user.GetRoles('timesong')
    print user.Check('timesong', '123456')
    
    customer = Customer()
    # customer.Insert('timesong', address1='china region 1')
    # customer.Insert('张三', address1='china region 2')
    # customer.UpdateById(1, 'hanmingsoft', address1='china ren')
    # customer.UpdateByName(u'张三', address1='test address 1')
    # print customer.GetAll()
    # print customer.GetById(1)
    customer.Insert({'code':'001', 'name':'zhang', 'tel':'110'})
    customer.Insert({'code':'002', 'name':'lining', 'tel':'110', 'address1':u'西安市'})
    customer.Insert({'code':'003', 'name':'zhao', 'tel':'120', 'address1':u'湖南省'})
    customer.Insert({'code':'004', 'name':'hu', 'tel':'0001', 'address1':u'TOKYO'})
    customer.UpdateById(1, {'tel':'119', 'email':'hu@tokyo.com', 'address2':u'沙市'})
    print customer.GetAll()
    print '--------------------'
    print customer.GetById(1)
    print '+++++++++++++++++++++'
    print customer.GetById(1, ['name', 'tel', 'address1'])
    print '*********************'
    print customer.GetMany(where="code in ('001', '002')", order="code, tel")
    
    for i in range(5, 100):
        customer.Insert({'code':'%02d' % i, 'name':'test%02d'%i, 'tel':'0001', 'address1':u'北京'})
